package dao.concret;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import dao.AbstractTable;
import dao.DAO;
import dao.tables.PERSONNE;

public class PersonneDAO extends DAO<PERSONNE> {

    @Override
    public PERSONNE find(long id) {
	PERSONNE pers = new PERSONNE();
	try {
	    Statement stmt = this.connect.createStatement(
		    ResultSet.TYPE_SCROLL_INSENSITIVE,
		    ResultSet.CONCUR_UPDATABLE);
	    ResultSet result = stmt
		    .executeQuery("SELECT * FROM redline.PERSONNE WHERE IDPERSONNE = "
			    + id);
	    if (result.first()) {
		pers = new PERSONNE(result.getLong("IDPERSONNE"),
			result.getString("NOMPERSONNE"),
			result.getString("PRENOMPERSONNE"),
			result.getString("CIVILITEPERSONNE"),
			result.getDate("DATENAISSANCEPERSONNE"));
	    }
	    stmt.close();
	    result.close();
	    stmt = null;
	    result = null;
	} catch (SQLException sqle) {
	    sqle.printStackTrace();
	}
	return pers;
    }

    @Override
    public PERSONNE create(AbstractTable at) {
	PERSONNE obj = (PERSONNE) at;

	try {
	    PreparedStatement prepare = this.connect
		    .prepareStatement("INSERT INTO redline.INSTRUMENT"
			    + " (NOMPERSONNE, PRENOMPERSONNE, CIVILITEPERSONNE, DATENAISSANCEPERSONNE)"
			    + " VALUES (?, ?, ?, ?)");
	    prepare.setString(1, obj.getNom());
	    prepare.setString(2, obj.getPrenom());
	    prepare.setString(3, obj.getCivilite());
	    prepare.setDate(4, obj.getDateDeNaissance());
	    prepare.executeUpdate();

	    ResultSet result = this.connect.createStatement(
		    ResultSet.TYPE_SCROLL_INSENSITIVE,
		    ResultSet.CONCUR_UPDATABLE).executeQuery(
		    "SELECT SEQ_PERS_IDPERS.currval AS id FROM dual");
	    result.first();
	    long id = result.getLong("id");

	    obj = this.find(id);
	    prepare.close();
	    result.close();
	    prepare = null;
	    result = null;
	} catch (SQLException sqle) {
	    sqle.printStackTrace();
	}
	return obj;
    }

    @Override
    public PERSONNE update(AbstractTable at) {
	PERSONNE obj = (PERSONNE) at;
	try {
	    PreparedStatement prepare = this.connect
		    .prepareStatement("UPDATE redline.PERSONNE SET NOMPERSONNE = ? , PRENOMPERSONNE = ?, CIVILITEPERSONNE = ?,"
			    + " DATENAISSANCEPERSONNE = ? WHERE IDPERSONNE = ?");
	    prepare.setString(1, obj.getNom());
	    prepare.setString(2, obj.getPrenom());
	    prepare.setString(3, obj.getCivilite());
	    prepare.setDate(4, obj.getDateDeNaissance());
	    prepare.setLong(5, obj.getId());
	    prepare.executeUpdate();

	    prepare.close();
	    prepare = null;
	} catch (SQLException sqle) {
	    sqle.printStackTrace();
	}
	return obj;
    }

    @Override
    public PERSONNE delete(PERSONNE at) {
	PERSONNE obj = (PERSONNE) at;
	try {
	    Statement stmt = this.connect.createStatement(
		    ResultSet.TYPE_SCROLL_INSENSITIVE,
		    ResultSet.CONCUR_UPDATABLE);
	    stmt.executeUpdate("DELETE FROM redline.PERSONNE WHERE id = "
		    + obj.getId());

	    stmt.close();
	    stmt = null;
	} catch (SQLException sqle) {
	    sqle.printStackTrace();
	}
	return obj;
    }

    @Override
    public PERSONNE[] list() {
	ArrayList<PERSONNE> list = new ArrayList<PERSONNE>();
	try {
	    Statement stmt = this.connect.createStatement(
		    ResultSet.TYPE_SCROLL_INSENSITIVE,
		    ResultSet.CONCUR_UPDATABLE);
	    ResultSet result = stmt
		    .executeQuery("SELECT IDPERSONNE FROM redline.PERSONNE");
	    while (result.next())
		list.add(this.find(result.getLong("IDPERSONNE")));

	    stmt.close();
	    result.close();
	    stmt = null;
	    result = null;
	} catch (SQLException sqle) {
	    sqle.printStackTrace();
	}
	return (PERSONNE[]) list.toArray(new PERSONNE[list.size()]);
    }

}
